-- RedFire 数据库迁移
-- 版本: 20250916002
-- 描述: 创建VnPy兼容的数据表
-- 创建时间: 2025-09-16 10:30:00

-- ===========================================
-- 向前迁移 (Forward Migration)
-- ===========================================

-- 创建Tick数据表 (分区表)
CREATE TABLE IF NOT EXISTS dbtickdata (
    id BIGSERIAL PRIMARY KEY,
    symbol VARCHAR(32) NOT NULL,
    exchange VARCHAR(16) NOT NULL,
    datetime TIMESTAMP NOT NULL,
    name VARCHAR(64),
    volume DECIMAL(20,4) DEFAULT 0,
    turnover DECIMAL(20,4) DEFAULT 0,
    open_interest DECIMAL(20,4) DEFAULT 0,
    last_price DECIMAL(10,4) DEFAULT 0,
    last_volume DECIMAL(20,4) DEFAULT 0,
    limit_up DECIMAL(10,4) DEFAULT 0,
    limit_down DECIMAL(10,4) DEFAULT 0,
    open_price DECIMAL(10,4) DEFAULT 0,
    high_price DECIMAL(10,4) DEFAULT 0,
    low_price DECIMAL(10,4) DEFAULT 0,
    pre_close DECIMAL(10,4) DEFAULT 0,
    -- 五档买盘
    bid_price_1 DECIMAL(10,4) DEFAULT 0,
    bid_price_2 DECIMAL(10,4) DEFAULT 0,
    bid_price_3 DECIMAL(10,4) DEFAULT 0,
    bid_price_4 DECIMAL(10,4) DEFAULT 0,
    bid_price_5 DECIMAL(10,4) DEFAULT 0,
    -- 五档卖盘
    ask_price_1 DECIMAL(10,4) DEFAULT 0,
    ask_price_2 DECIMAL(10,4) DEFAULT 0,
    ask_price_3 DECIMAL(10,4) DEFAULT 0,
    ask_price_4 DECIMAL(10,4) DEFAULT 0,
    ask_price_5 DECIMAL(10,4) DEFAULT 0,
    -- 五档买量
    bid_volume_1 DECIMAL(20,4) DEFAULT 0,
    bid_volume_2 DECIMAL(20,4) DEFAULT 0,
    bid_volume_3 DECIMAL(20,4) DEFAULT 0,
    bid_volume_4 DECIMAL(20,4) DEFAULT 0,
    bid_volume_5 DECIMAL(20,4) DEFAULT 0,
    -- 五档卖量
    ask_volume_1 DECIMAL(20,4) DEFAULT 0,
    ask_volume_2 DECIMAL(20,4) DEFAULT 0,
    ask_volume_3 DECIMAL(20,4) DEFAULT 0,
    ask_volume_4 DECIMAL(20,4) DEFAULT 0,
    ask_volume_5 DECIMAL(20,4) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (datetime);

-- 创建Tick数据分区 (按月分区)
CREATE TABLE IF NOT EXISTS dbtickdata_2024_09 PARTITION OF dbtickdata
    FOR VALUES FROM ('2024-09-01') TO ('2024-10-01');
CREATE TABLE IF NOT EXISTS dbtickdata_2024_10 PARTITION OF dbtickdata
    FOR VALUES FROM ('2024-10-01') TO ('2024-11-01');
CREATE TABLE IF NOT EXISTS dbtickdata_2024_11 PARTITION OF dbtickdata
    FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');
CREATE TABLE IF NOT EXISTS dbtickdata_2024_12 PARTITION OF dbtickdata
    FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');
CREATE TABLE IF NOT EXISTS dbtickdata_2025_01 PARTITION OF dbtickdata
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- 创建K线数据表 (分区表)
CREATE TABLE IF NOT EXISTS dbbardata (
    id BIGSERIAL PRIMARY KEY,
    symbol VARCHAR(32) NOT NULL,
    exchange VARCHAR(16) NOT NULL,
    datetime TIMESTAMP NOT NULL,
    interval VARCHAR(8) NOT NULL,
    volume DECIMAL(20,4) DEFAULT 0,
    turnover DECIMAL(20,4) DEFAULT 0,
    open_interest DECIMAL(20,4) DEFAULT 0,
    open_price DECIMAL(10,4) DEFAULT 0,
    high_price DECIMAL(10,4) DEFAULT 0,
    low_price DECIMAL(10,4) DEFAULT 0,
    close_price DECIMAL(10,4) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) PARTITION BY RANGE (datetime);

-- 创建K线数据分区 (按月分区)
CREATE TABLE IF NOT EXISTS dbbardata_2024_09 PARTITION OF dbbardata
    FOR VALUES FROM ('2024-09-01') TO ('2024-10-01');
CREATE TABLE IF NOT EXISTS dbbardata_2024_10 PARTITION OF dbbardata
    FOR VALUES FROM ('2024-10-01') TO ('2024-11-01');
CREATE TABLE IF NOT EXISTS dbbardata_2024_11 PARTITION OF dbbardata
    FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');
CREATE TABLE IF NOT EXISTS dbbardata_2024_12 PARTITION OF dbbardata
    FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');
CREATE TABLE IF NOT EXISTS dbbardata_2025_01 PARTITION OF dbbardata
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

-- ===========================================
-- 创建索引
-- ===========================================

-- Tick数据索引
CREATE INDEX IF NOT EXISTS idx_dbtickdata_symbol_datetime ON dbtickdata (symbol, datetime);
CREATE INDEX IF NOT EXISTS idx_dbtickdata_exchange_datetime ON dbtickdata (exchange, datetime);
CREATE INDEX IF NOT EXISTS idx_dbtickdata_datetime ON dbtickdata (datetime);

-- K线数据索引
CREATE INDEX IF NOT EXISTS idx_dbbardata_symbol_interval_datetime ON dbbardata (symbol, interval, datetime);
CREATE INDEX IF NOT EXISTS idx_dbbardata_exchange_datetime ON dbbardata (exchange, datetime);
CREATE INDEX IF NOT EXISTS idx_dbbardata_datetime ON dbbardata (datetime);

-- ===========================================
-- 创建概览表
-- ===========================================

-- Tick数据概览表
CREATE TABLE IF NOT EXISTS dbtickoverview (
    id BIGSERIAL PRIMARY KEY,
    symbol VARCHAR(32) NOT NULL,
    exchange VARCHAR(16) NOT NULL,
    count BIGINT DEFAULT 0,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uk_tick_symbol_exchange UNIQUE (symbol, exchange)
);

-- K线数据概览表
CREATE TABLE IF NOT EXISTS dbbaroverview (
    id BIGSERIAL PRIMARY KEY,
    symbol VARCHAR(32) NOT NULL,
    exchange VARCHAR(16) NOT NULL,
    interval VARCHAR(8) NOT NULL,
    count BIGINT DEFAULT 0,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uk_bar_symbol_exchange_interval UNIQUE (symbol, exchange, interval)
);

-- ===========================================
-- 创建存储过程和函数
-- ===========================================

-- 更新概览表的存储过程
CREATE OR REPLACE FUNCTION update_overview_tables()
RETURNS void AS $$
BEGIN
    -- 更新Tick概览
    INSERT INTO dbtickoverview (symbol, exchange, count, start_time, end_time)
    SELECT symbol, exchange, COUNT(*) as count,
           MIN(datetime) as start_time, MAX(datetime) as end_time
    FROM dbtickdata
    GROUP BY symbol, exchange
    ON CONFLICT (symbol, exchange) DO UPDATE SET
        count = EXCLUDED.count,
        start_time = EXCLUDED.start_time,
        end_time = EXCLUDED.end_time,
        updated_at = CURRENT_TIMESTAMP;
    
    -- 更新K线概览
    INSERT INTO dbbaroverview (symbol, exchange, interval, count, start_time, end_time)
    SELECT symbol, exchange, interval, COUNT(*) as count,
           MIN(datetime) as start_time, MAX(datetime) as end_time
    FROM dbbardata
    GROUP BY symbol, exchange, interval
    ON CONFLICT (symbol, exchange, interval) DO UPDATE SET
        count = EXCLUDED.count,
        start_time = EXCLUDED.start_time,
        end_time = EXCLUDED.end_time,
        updated_at = CURRENT_TIMESTAMP;
END;
$$ LANGUAGE plpgsql;

-- 数据质量检查函数
CREATE OR REPLACE FUNCTION check_data_completeness(
    table_name_param VARCHAR(64), 
    check_date DATE
)
RETURNS DECIMAL(5,4) AS $$
DECLARE
    total_expected INT := 0;
    total_actual INT := 0;
    completeness DECIMAL(5,4) := 0.0;
BEGIN
    -- 检查当日数据完整性
    IF table_name_param = 'dbtickdata' THEN
        EXECUTE format('SELECT COUNT(*) FROM %I WHERE DATE(datetime) = $1', table_name_param)
        INTO total_actual USING check_date;
        
        -- 假设每日预期有100万条Tick数据
        total_expected := 1000000;
    END IF;
    
    IF total_expected > 0 THEN
        completeness := total_actual::DECIMAL / total_expected;
    END IF;
    
    RETURN completeness;
END;
$$ LANGUAGE plpgsql;

-- ===========================================
-- 回滚说明 (Rollback Instructions)
-- ===========================================

-- 如需回滚，请手动执行以下操作:
-- 1. 删除函数:
--    DROP FUNCTION IF EXISTS check_data_completeness(VARCHAR, DATE);
--    DROP FUNCTION IF EXISTS update_overview_tables();
-- 2. 删除概览表:
--    DROP TABLE IF EXISTS dbbaroverview;
--    DROP TABLE IF EXISTS dbtickoverview;
-- 3. 删除数据表:
--    DROP TABLE IF EXISTS dbbardata;
--    DROP TABLE IF EXISTS dbtickdata;
-- 4. 从迁移记录中删除此版本:
--    DELETE FROM schema_migrations WHERE version = '20250916002';
